﻿using Shared.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace Shared.Method
{
    public class ObjectToSQL
    {
        public static string ObjectToInsertSql<T>(T obj) where T : TableBase
        {
            if (obj == null) return string.Empty;

            PropertyInfo[] infos = typeof(T).GetProperties();

            if (infos == null) return string.Empty;
            List<string> insertList = obj.GetListUpdate();

            if (insertList == null || insertList.Count == 0) return string.Empty;

            StringBuilder sqlCols = new StringBuilder();
            StringBuilder sqlValues = new StringBuilder();

            for (int i = 0; i < infos.Length; i++)
            {
                if (insertList.Contains(infos[i].Name) == false) continue;

                sqlCols.Append($"{infos[i].Name},");
                //sqlCols.Append(",");

                //two single quotation mask means single quotation symbol

                if (infos[i].GetValue(obj) == null)
                {
                    sqlValues.Append("NULL,");
                }
                else if (infos[i].PropertyType == typeof(string))
                {
                    sqlValues.Append($"'{infos[i].GetValue(obj).ToString().Replace("'", "''")}',");
                }
                else if (
                    infos[i].PropertyType == typeof(DateTime) ||
                    infos[i].PropertyType == typeof(DateTime?) ||
                    infos[i].PropertyType == typeof(bool) ||
                    infos[i].PropertyType == typeof(bool?))
                {
                    sqlValues.Append($"'{infos[i].GetValue(obj)}',");
                }
                else
                {
                    sqlValues.Append($"{infos[i].GetValue(obj)},");
                }
            }

            sqlCols.Remove(sqlCols.Length - 1, 1);
            sqlValues.Remove(sqlValues.Length - 1, 1);

            string sql = $"INSERT INTO {typeof(T).Name}  ({sqlCols}) VALUES ({sqlValues}) select @@identity";

            return sql;
        }

        public static string ObjectToUpdateSql<T>(T obj) where T : TableBase
        {
            if (obj == null) return string.Empty;

            List<string> whereList = obj.GetListWhere();
            List<string> updateList = obj.GetListUpdate();

            StringBuilder whereBuf = new StringBuilder();

            PropertyInfo info;

            for (int i = 0; whereList != null && whereList.Count > 0 && i < whereList.Count; i++)
            {
                info = typeof(T).GetProperty(whereList[i]);

                if (info.GetValue(obj) == null)
                {
                    whereBuf.Append($"{info.Name}=NULL ");
                }
                else if (info.PropertyType == typeof(string))
                {
                    whereBuf.Append($"{info.Name} = '{info.GetValue(obj).ToString().Replace("'", "''")}' ");
                }
                else if (
                    info.PropertyType == typeof(DateTime) ||
                    info.PropertyType == typeof(bool) ||
                    info.PropertyType == typeof(DateTime?) ||
                    info.PropertyType == typeof(bool?))
                {
                    whereBuf.Append($"{info.Name} = '{info.GetValue(obj)}' ");
                }
                else
                {
                    whereBuf.Append($"{info.Name} = {info.GetValue(obj)} ");
                }

                if (i != whereList.Count - 1)
                {
                    whereBuf.Append("AND ");
                }
            }

            string whereSql = obj.GetStrWhereSQL();

            if (string.IsNullOrEmpty(whereSql) == false)
            {
                if (whereList != null && whereList.Count > 0)
                {
                    whereBuf.Append("AND ");
                }

                whereBuf.Append(whereSql);
            }

            StringBuilder updateBuf = new StringBuilder();

            for (int i = 0; updateList != null && updateList.Count > 0 && i < updateList.Count; i++)
            {
                info = typeof(T).GetProperty(updateList[i]);

                if (info.GetValue(obj) == null)
                {
                    updateBuf.Append($"{info.Name}=NULL");
                }
                else if (info.PropertyType == typeof(string))
                {
                    updateBuf.Append($"{info.Name} = '{info.GetValue(obj).ToString().Replace("'", "''")}' ");
                }
                else if (
                    info.PropertyType == typeof(DateTime) ||
                    info.PropertyType == typeof(bool) ||
                    info.PropertyType == typeof(DateTime?) ||
                    info.PropertyType == typeof(bool?))
                {
                    updateBuf.Append($"{info.Name} = '{info.GetValue(obj)}'");
                }
                else
                {
                    updateBuf.Append($"{info.Name} = {info.GetValue(obj)}");
                }

                if (i != updateList.Count - 1)
                {
                    updateBuf.Append(",");
                }
            }

            string sql = $"UPDATE {typeof(T).Name} SET {updateBuf} WHERE {whereBuf}";

            return sql;
        }

        public static string ObjectToDeleteSql<T>(T obj) where T : TableBase
        {
            if (obj == null) return string.Empty;

            List<string> whereList = obj.GetListWhere();

            StringBuilder whereBuf = new StringBuilder();


            PropertyInfo info;

            for (int i = 0; whereList != null && whereList.Count > 0 && i < whereList.Count; i++)
            {
                info = typeof(T).GetProperty(whereList[i]);

                if (info.GetValue(obj) == null)
                {
                    whereBuf.Append($"{info.Name}=NULL ");
                }
                else if (info.PropertyType == typeof(string))
                {
                    whereBuf.Append($"{info.Name} = '{info.GetValue(obj).ToString().Replace("'", "''")}' ");
                }
                else if (
                    info.PropertyType == typeof(DateTime) ||
                    info.PropertyType == typeof(bool) ||
                    info.PropertyType == typeof(DateTime?) ||
                    info.PropertyType == typeof(bool?))
                {
                    whereBuf.Append($"{info.Name} = '{info.GetValue(obj)}' ");
                }
                else
                {
                    whereBuf.Append($"{info.Name} = {info.GetValue(obj)} ");
                }

                if (i != whereList.Count - 1)
                {
                    whereBuf.Append("AND ");
                }
            }

            string whereSql = obj.GetStrWhereSQL();

            if (string.IsNullOrEmpty(whereSql) == false)
            {
                if (whereList != null && whereList.Count > 0)
                {
                    whereBuf.Append(" AND ");
                }
                whereBuf.Append(whereSql);
            }

            string sql = $"DELETE FROM {typeof(T).Name} WHERE {whereBuf}";
            //LogHelper.WriteDebugLogTxt("DELETE SQL", sql);
            return sql;
        }

        public static string ObjectToSelectCountSql<T>(T obj) where T : TableBase
        {
            if (obj == null) return string.Empty;

            List<string> whereList = obj.GetListWhere();

            StringBuilder whereBuf = new StringBuilder();

            PropertyInfo info;

            for (int i = 0; whereList != null && whereList.Count > 0 && i < whereList.Count; i++)
            {
                info = typeof(T).GetProperty(whereList[i]);

                if (info.GetValue(obj) == null)
                {
                    whereBuf.Append($"{info.Name}=NULL ");
                }
                else if (info.PropertyType == typeof(string))
                {
                    whereBuf.Append($"{info.Name} = '{info.GetValue(obj).ToString().Replace("'", "''")}' ");
                }
                else if (
                    info.PropertyType == typeof(DateTime) ||
                    info.PropertyType == typeof(bool) ||
                    info.PropertyType == typeof(DateTime?) ||
                    info.PropertyType == typeof(bool?))
                {
                    whereBuf.Append($"{info.Name} = '{info.GetValue(obj)}' ");
                }
                else
                {
                    whereBuf.Append($"{info.Name} = {info.GetValue(obj)} ");
                }

                if (i != whereList.Count - 1)
                {
                    whereBuf.Append("AND ");
                }
            }

            string whereSql = obj.GetStrWhereSQL();

            if (string.IsNullOrEmpty(whereSql) == false)
            {
                if (whereList != null && whereList.Count > 0)
                {
                    whereBuf.Append(" AND ");
                }
                whereBuf.Append(whereSql);
            }

            string sql = $"SELECT COUNT(*) FROM {typeof(T).Name} WHERE {whereBuf}";
            //LogHelper.WriteDebugLogTxt("DELETE SQL", sql);
            return sql;
        }

        public static string ObjectToSelectSql<T>(T obj) where T : TableBase
        {
            return ObjectToSelectCountSql(obj).Replace("COUNT(*)", "*");
        }
    }
}
